package com.intelligentmeterreadingsys.report.db;

import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import com.intelligentmeterreadingsys.BaseDao.BaseDao;
import com.intelligentmeterreadingsys.povo.EngineeringOrderPoVo;
import com.intelligentmeterreadingsys.povo.TypePoVo;
import com.intelligentmeterreadingsys.povo.UserWaterVO;
import com.intelligentmeterreadingsys.povo.WaterInfoVO;

import dbhelper.DbTableName;

/**
 * 工程单增、删除、改、查询
 * 
 * @author king
 * 
 */
public class ReportDao extends BaseDao {

    public SQLiteDatabase db;

    public ReportDao(Context context) {
	super(context);
	this.db = super.getDb();
    }

    /**
     * 添加工程单
     * 
     * @param values
     * @return
     */
    public int insertReport(ContentValues values, String ordertype) {
	int id = 0;
	String sql1 = "select number from type where name='" + ordertype + "' and status='2'";
	Cursor cursor1 = db.rawQuery(sql1, null);
	String orderid = "";
	if (cursor1.moveToNext()) {
	    orderid = cursor1.getString(cursor1.getColumnIndex("number"));

	}
	if (cursor1 != null) {
	    cursor1.close();
	}
	System.out.println("orderid" + orderid);
	values.put("ordertype", orderid);
	long rowid = db.insert(DbTableName.ENGINEERINGORDER, null, values);

	if (rowid > 0) {
	    String sql = "select max(_id) as max_id from engineeringOrder";
	    Cursor cursor = db.rawQuery(sql, null);
	    if (cursor.moveToNext()) {
		id = cursor.getInt(cursor.getColumnIndex("max_id"));
	    }

	    if (cursor != null) {
		cursor.close();
	    }

	} else {
	    return 0;
	}

	return id;

    }

    /**
     * 查询工程单类型
     * 
     * @return
     */
    public List<TypePoVo> queryOrderType() {
	List<TypePoVo> list = new ArrayList<TypePoVo>();
	String sql = "select * from type where status='2'";
	Cursor cursor = db.rawQuery(sql, null);

	if (cursor == null) {

	    return null;
	}
	while (cursor.moveToNext()) {
	    TypePoVo type = new TypePoVo();
	    type.setName(cursor.getString(cursor.getColumnIndex("name")));
	    type.setNumber(cursor.getString(cursor.getColumnIndex("number")));
	    System.out.println(cursor.getString(cursor.getColumnIndex("number")));
	    list.add(type);
	}

	if (cursor != null) {
	    cursor.close();
	}
	return list;
    }

    /**
     * 修改工程单
     * 
     * @param value
     * @return
     */
    public boolean modifyOrder(ContentValues value, String id, String type) {
	boolean b = false;
	db.delete(DbTableName.RESOURCES, "otherid=? and status=?", new String[] { id, "1" });
	String sql = "select number from type where name='" + type + "' and status='2'";
	Cursor cursor = db.rawQuery(sql, null);
	String ordertype = "";
	if (cursor.moveToNext()) {
	    ordertype = cursor.getString(cursor.getColumnIndex("number"));
	}
	value.put("ordertype", ordertype);
	if (cursor != null) {
	    cursor.close();
	}
	int i = db.update(DbTableName.ENGINEERINGORDER, value, "_id=?", new String[] { id });

	if (i > 0) {
	    b = true;
	}

	return b;
    }

    /**
     * 查询工程单
     * 
     * @param number水表编号
     * @return
     */
    public List<EngineeringOrderPoVo> queryOrder(String orderid) {

	List<EngineeringOrderPoVo> list = new ArrayList<EngineeringOrderPoVo>();

	String sql = "select e.[_id],e.[status],e.[waternumber],e.[ordertype],e.[ordername],e.[desc],e.[imagepath],e.[videopath],"
		+ "e.[soundpath],t.[name] from engineeringorder e,type t where e.[ordertype]=t.[number] and t.[status]='2'";

	if (orderid != null) {
	    sql = sql + " and  e.[_id]='" + orderid + "'";
	}
	sql = sql + " order by e.[_id] desc";
	System.out.println(sql);
	Cursor cursor = db.rawQuery(sql, null);

	while (cursor.moveToNext()) {
	    EngineeringOrderPoVo order = new EngineeringOrderPoVo();
	    order.setDesc(cursor.getString(cursor.getColumnIndex("desc")));
	    order.setTypenumber(cursor.getString(cursor.getColumnIndex("ordertype")));
	    order.setName(cursor.getString(cursor.getColumnIndex("ordername")));
	    order.setWaternumber(cursor.getString(cursor.getColumnIndex("waternumber")));

	    order.setStatus(cursor.getString(cursor.getColumnIndex("status")));
	    order.setVideopath(cursor.getString(cursor.getColumnIndex("videopath")));
	    order.setSoundpath(cursor.getString(cursor.getColumnIndex("soundpath")));
	    order.setTypename(cursor.getString(cursor.getColumnIndex("name")));
	    order.setId(cursor.getString(cursor.getColumnIndex("_id")));

	    list.add(order);

	}
	if (cursor != null) {
	    cursor.close();
	}

	return list;

    };

    /**
     * 删除一个工程单
     * 
     * @param id
     * @return
     */
    public boolean delectOrder(String id) {

	return super.delect(id, DbTableName.ENGINEERINGORDER);
    }

    /**
     * 通过名称查询工程类型
     * 
     * @param name
     * @return
     */
    public String queryTypeID(String name) {
	String id = "";
	String sql = "select * from type where status='2' and name='" + name + "'";

	Cursor cursor = db.rawQuery(sql, null);
	if (cursor.moveToNext()) {
	    id = cursor.getString(cursor.getColumnIndex("number"));
	}
	if (cursor != null) {
	    cursor.close();
	}
	return id;

    }

    /**
     * 通过RFID号找到水表编号
     * 
     * @param rfid
     * @return
     */
    public WaterInfoVO queryWaterNumber(String rfid) {

	String sql = "select waternumber ,latitude, longitude from waterinfo where RFIDnumber='" + rfid + "' ";

	Cursor cursor = db.rawQuery(sql, null);
	WaterInfoVO water = null;
	if (cursor.moveToNext()) {
	    water = new WaterInfoVO();
	    water.setWaternumber(cursor.getString(cursor.getColumnIndex("waternumber")));
	    water.setLatitude(cursor.getDouble(cursor.getColumnIndex("latitude")));
	    water.setLongitude(cursor.getDouble(cursor.getColumnIndex("longitude")));
	}
	if (cursor != null) {
	    cursor.close();
	}
	return water;

    }

    /**
     * 查询工程单总数据
     * 
     * @return
     */
    public int queryOrderTotal() {
	String sql = "Select _id From engineeringOrder ";
	Cursor cursor = db.rawQuery(sql, null);
	int i = cursor.getCount();
	if (cursor != null) {
	    cursor.close();
	}
	return i;

    }

    /**
     * 查询未上传工程单数
     * 
     * @return
     */
    public int queryOrderUpload() {
	String sql = "select _id from engineeringOrder where status='0'";
	Cursor cursor = db.rawQuery(sql, null);
	int i = cursor.getCount();
	if (cursor != null) {
	    cursor.close();
	}
	return i;

    }

    /**
     * 查询所需要上传的工程单
     * 
     * @return
     */
    public List<EngineeringOrderPoVo> queryUpload(String orderid) {

	List<EngineeringOrderPoVo> list = new ArrayList<EngineeringOrderPoVo>();
	String sql = "select * from engineeringorder e where e.[status]='0'";
	if (orderid != null) {
	    sql = sql + " and e.[_id]='" + orderid + "'";
	}
	Cursor cursor = db.rawQuery(sql, null);
	Cursor cur = null;
	while (cursor.moveToNext()) {
	    EngineeringOrderPoVo order = new EngineeringOrderPoVo();
	    order.setDesc(cursor.getString(cursor.getColumnIndex("desc")));
	    order.setId(cursor.getString(cursor.getColumnIndex("_id")));
	    order.setName(cursor.getString(cursor.getColumnIndex("ordername")));
	    order.setSoundpath(cursor.getString(cursor.getColumnIndex("soundpath")));
	    order.setTypenumber(cursor.getString(cursor.getColumnIndex("ordertype")));
	    order.setWaternumber(cursor.getString(cursor.getColumnIndex("waternumber")));
	    order.setVideopath(cursor.getString(cursor.getColumnIndex("videopath")));
	    order.setLatitude(cursor.getDouble(cursor.getColumnIndex("latitude")));
	    order.setLongitude(cursor.getDouble(cursor.getColumnIndex("longitude")));
	    String reoouseSQL = "select * from " + DbTableName.RESOURCES + " where otherid='" + order.getId() + "' and type='1'" + "and status='1'";
	    cur = db.rawQuery(reoouseSQL, null);
	    List<String> listimg = new ArrayList<String>();
	    while (cur.moveToNext()) {

		listimg.add(cur.getString(cur.getColumnIndex("path")));
	    }
	    if (cur != null) {
		cur.close();
	    }
	    order.setImagepath(listimg);
	    list.add(order);
	}

	if (cursor != null) {
	    cursor.close();
	}

	return list;

    }

    /**
     * 上传成功后更新状态
     * 
     * @param id
     * @return
     */
    public boolean modifyStatus(int id) {
	ContentValues value = new ContentValues();
	value.put("status", "1");
	return super.modify("_id=?", DbTableName.ENGINEERINGORDER, value);
    }

    /**
     * 删除图片路径
     * 
     * @param otherid
     * @return
     */
    public boolean delectImg(String otherid) {
	System.out.println("otherid------->" + otherid);
	boolean b = false;

	int id = db.delete(DbTableName.RESOURCES, "otherid=? and  status=?", new String[] { otherid, "1" });
	if (id > 0) {
	    b = true;
	}
	return b;
    }

    /**
     * 添加资源（图片、视频、声音）
     * 
     * @param list
     * @return
     */
    public boolean addResources(List<ContentValues> list) {

	return super.insertALL(list, DbTableName.RESOURCES);

    }

    /**
     * 查询工程单图片
     * 
     * @param otherid
     * @return
     */
    public ArrayList<String> queryImagePath(String otherid) {

	ArrayList<String> list = new ArrayList<String>();
	String sql = "select * from " + DbTableName.RESOURCES + " where otherid='" + otherid + "' and type='1' " + "and status='1' order by _id desc";

	Cursor cursor = db.rawQuery(sql, null);
	while (cursor.moveToNext()) {

	    list.add(cursor.getString(cursor.getColumnIndex("path")));
	}
	if (cursor != null) {
	    cursor.close();
	}
	return list;
    }

    /**
     * 通过RFID卡号查询用户及水表信息
     * 
     * @param RFID
     * @return
     */
    public List<UserWaterVO> queryUserWaterInfo(String RFID, String waterid) {
	List<UserWaterVO> list = new ArrayList<UserWaterVO>();
	UserWaterVO water = null;
	String sql = "select w.[_id] as waterid,w.[isupload],w.[thisdate],w.[desc],w.[thismonthwater], w.[waternumber],w.[usernumber],w.[wateradds], w.[lastmonthwater]"
		+ " ,w.[waterproperties],u.[phone],w.[metercaliber],u.[username],w.[alarmlimit],w.[lowalarmlimit] from waterinfo w,userinfo u where "
		+ "w.[usernumber]=u.[usernumber] ";
	if (RFID != null) {
	    sql = sql + " and w.[RFIDnumber]='" + RFID + "'";
	}

	if (waterid != null) {
	    sql = sql + " and w.[_id]=" + waterid;
	}
	System.out.println("读卡SQL" + sql);

	Cursor cursor = db.rawQuery(sql, null);
	while (cursor.moveToNext()) {
	    water = new UserWaterVO();
	    water.setIsupload(cursor.getString(cursor.getColumnIndex("isupload")));
	    water.setAdds(cursor.getString(cursor.getColumnIndex("wateradds")));
	    water.setLastmonthwater(cursor.getDouble(cursor.getColumnIndex("lastmonthwater")));
	    water.setMetercaliber(cursor.getString(cursor.getColumnIndex("metercaliber")));
	    water.setName(cursor.getString(cursor.getColumnIndex("username")));
	    water.setNumber(cursor.getString(cursor.getColumnIndex("usernumber")));
	    water.setWaternumber(cursor.getString(cursor.getColumnIndex("waternumber")));
	    water.setAlarmlimit(cursor.getDouble(cursor.getColumnIndex("alarmlimit")));
	    water.setLowalarmlimit(cursor.getDouble(cursor.getColumnIndex("lowalarmlimit")));
	    water.setWaterid(cursor.getString(cursor.getColumnIndex("waterid")));
	    water.setThisdate(cursor.getString(cursor.getColumnIndex("thisdate")));
	    String thismonthwater = cursor.getString(cursor.getColumnIndex("thismonthwater"));
	    water.setDesc(cursor.getString(cursor.getColumnIndex("desc")));
	    if (thismonthwater != null && !thismonthwater.equals("")) {
		water.setThismonthwater(cursor.getDouble(cursor.getColumnIndex("thismonthwater")));
	    } else {
		water.setThismonthwater(0.0);
	    }
	    water.setWaterproperties(cursor.getString(cursor.getColumnIndex("waterproperties")));
	    water.setPhone(cursor.getString(cursor.getColumnIndex("phone")));
	    list.add(water);
	}
	if (cursor != null) {
	    cursor.close();
	}
	return list;

    }

    public void close() {
	if (db != null && db.isOpen()) {
	    db.close();
	    super.close();
	}
    }
}
